package com.eagle.security.dao.impl;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContextException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.security.access.AccessDeniedException;
import org.springframework.security.authentication.AuthenticationManager;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.AuthenticationException;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.AuthorityUtils;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UserCache;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.cache.NullUserCache;
import org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl;
import org.springframework.util.Assert;

import com.eagle.security.dao.GroupDao;
import com.eagle.security.dao.UserDetailsDao;
import com.eagle.security.entries.OverdueTypeConstant;
import com.eagle.security.entries.RoleGroup;
import com.eagle.security.entries.User;
import com.eagle.security.entries.UserExtendInfo;

/**
 * 权限安全管理实现类。
 * 
 * @author Luke Taylor , xugang
 * @since 2.0
 */

public class UserSecurityDao extends JdbcDaoImpl implements UserDetailsDao, GroupDao  {
    //~ Static fields/initializers =====================================================================================

    // UserDetailsManager SQL
    public static final String DEF_CREATE_USER_SQL =
            "insert into users (username, password, enabled,nick_name) values (?,?,?,?)";
    public static final String DEF_DELETE_USER_SQL =
            "delete from users where username = ?";
    public static final String DEF_UPDATE_USER_SQL =
            "update users set password = ?, enabled = ?, nick_name=?  where username = ?";
    public static final String DEF_INSERT_AUTHORITY_SQL =
            "insert into authorities (username, authority) values (?,?)";
    public static final String DEF_DELETE_USER_AUTHORITIES_SQL =
            "delete from authorities where username = ?";
    public static final String DEF_USER_EXISTS_SQL =
            "select username from users where username = ?";
    public static final String DEF_CHANGE_PASSWORD_SQL =
            "update users set password = ? where username = ?";

    // GroupManager SQL
    public static final String DEF_FIND_GROUPS_SQL =
            "select group_name from groups";
    public static final String DEF_FIND_USERS_IN_GROUP_SQL =
            "select username from group_members gm, groups g " +
            "where gm.group_id = g.id" +
            " and g.group_name = ?";
    public static final String DEF_INSERT_GROUP_SQL =
            "insert into groups (group_name) values (?)";
    public static final String DEF_FIND_GROUP_ID_SQL =
            "select id from groups where group_name = ?";
    public static final String DEF_INSERT_GROUP_AUTHORITY_SQL =
            "insert into group_authorities (group_id, authority) values (?,?)";
    public static final String DEF_DELETE_GROUP_SQL =
            "delete from groups where id = ?";
    public static final String DEF_DELETE_GROUP_AUTHORITIES_SQL =
            "delete from group_authorities where group_id = ?";
    public static final String DEF_DELETE_GROUP_MEMBERS_SQL =
            "delete from group_members where group_id = ?";
    public static final String DEF_RENAME_GROUP_SQL =
            "update groups set group_name = ? where group_name = ?";
    public static final String DEF_INSERT_GROUP_MEMBER_SQL =
            "insert into group_members (group_id, username) values (?,?)";
    public static final String DEF_DELETE_GROUP_MEMBER_SQL =
            "delete from group_members where group_id = ? and username = ?";
    public static final String DEF_GROUP_AUTHORITIES_QUERY_SQL =
            "select g.id, g.group_name, ga.authority " +
            "from groups g, group_authorities ga " +
            "where g.group_name = ? " +
            "and g.id = ga.group_id ";
    public static final String DEF_DELETE_GROUP_AUTHORITY_SQL =
            "delete from group_authorities where group_id = ? and authority = ?";

    // jdbcDaoImpl SQL
    public static final String DEF_MY_USERS_BY_USERNAME_QUERY =
            "select username,password,enabled, nick_name " +
            "from users " +
            "where username = ?";
    public static final String DEF_MY_AUTHORITIES_BY_USERNAME_QUERY =
            "select username,authority " +
            "from authorities " +
            "where username = ?";
    public static final String DEF_MY_GROUP_AUTHORITIES_BY_USERNAME_QUERY =
            "select g.id, g.group_name, ga.authority " +
            "from groups g, group_members gm, group_authorities ga " +
            "where gm.username = ? " +
            "and g.id = ga.group_id " +
            "and g.id = gm.group_id";

    
    //~ Instance fields ================================================================================================

    protected final Log logger = LogFactory.getLog(getClass());

    private String createUserSql = DEF_CREATE_USER_SQL;
    private String deleteUserSql = DEF_DELETE_USER_SQL;
    private String updateUserSql = DEF_UPDATE_USER_SQL;
    private String createAuthoritySql = DEF_INSERT_AUTHORITY_SQL;
    private String deleteUserAuthoritiesSql = DEF_DELETE_USER_AUTHORITIES_SQL;
    private String userExistsSql = DEF_USER_EXISTS_SQL;
    private String changePasswordSql = DEF_CHANGE_PASSWORD_SQL;

    private String findAllGroupsSql = DEF_FIND_GROUPS_SQL;
    private String findUsersInGroupSql = DEF_FIND_USERS_IN_GROUP_SQL;
    private String insertGroupSql = DEF_INSERT_GROUP_SQL;
    private String findGroupIdSql = DEF_FIND_GROUP_ID_SQL;
    private String insertGroupAuthoritySql = DEF_INSERT_GROUP_AUTHORITY_SQL;
    private String deleteGroupSql = DEF_DELETE_GROUP_SQL;
    private String deleteGroupAuthoritiesSql = DEF_DELETE_GROUP_AUTHORITIES_SQL;
    private String deleteGroupMembersSql = DEF_DELETE_GROUP_MEMBERS_SQL;
    private String renameGroupSql = DEF_RENAME_GROUP_SQL;
    private String insertGroupMemberSql = DEF_INSERT_GROUP_MEMBER_SQL;
    private String deleteGroupMemberSql = DEF_DELETE_GROUP_MEMBER_SQL;
    private String groupAuthoritiesSql = DEF_GROUP_AUTHORITIES_QUERY_SQL;
    private String deleteGroupAuthoritySql = DEF_DELETE_GROUP_AUTHORITY_SQL;

    
    
    private AuthenticationManager authenticationManager;

    private UserCache userCache = new NullUserCache();

    //~ Methods ========================================================================================================

    protected void initDao() throws ApplicationContextException {
        if (authenticationManager == null) {
            logger.info("No authentication manager set. Reauthentication of users when changing passwords will " +
                    "not be performed.");
        }
        //扩展/重置 父类jdbcDaoImpl的三个基本sql语句的值。
        setUsersByUsernameQuery(DEF_MY_USERS_BY_USERNAME_QUERY) ;
        setAuthoritiesByUsernameQuery(DEF_MY_AUTHORITIES_BY_USERNAME_QUERY);
        setGroupAuthoritiesByUsernameQuery(DEF_MY_GROUP_AUTHORITIES_BY_USERNAME_QUERY);
        
        super.initDao();
    }

  //~ 扩展 JdbcDaoImpl  protected  ==============================================================================
    @Override
    protected List<UserDetails> loadUsersByUsername(String username) {
        //getUsersByUsernameQuery()
        return getJdbcTemplate().query( DEF_MY_USERS_BY_USERNAME_QUERY, new String[] { username }, new RowMapper<UserDetails>() {
            public UserDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
                String username = rs.getString(1);
                String password = rs.getString(2);
                boolean enabled = rs.getBoolean(3);
                String nickname = rs.getString(4);
//                UserExtendInfo extendInfo = JsonUtils.convert(rs.getString(5), UserExtendInfo.class);
//                Timestamp createDate = rs.getTimestamp(6);
//                Timestamp startUseTime = rs.getTimestamp(7);
//                Timestamp endUseTime = rs.getTimestamp(8);
//                String remark = rs.getString(9);
//                int overdueType = rs.getInt(10);
                User user = new User(username, password, enabled, true, true, true, AuthorityUtils.NO_AUTHORITIES, username, nickname);
//                if (extendInfo != null) {
//                    user.setExtendInfo(extendInfo);
//                }
//                if (createDate != null) {
//                    user.setCreateDate(createDate);
//                }
//                user.setStartUseTime(startUseTime);
//                user.setEndUseTime(endUseTime);
//                user.setRemark(remark);
//                user.setOverdueType(overdueType);
                return user;
            }
        });
    }
    
    @Override
    protected UserDetails createUserDetails(String username, UserDetails userFromUserQuery, List<GrantedAuthority> combinedAuthorities) {
        String returnUsername = userFromUserQuery.getUsername();

        if (!isUsernameBasedPrimaryKey()) {
            returnUsername = username;
        }

        String id = returnUsername;
        String nickname = returnUsername;
        UserExtendInfo extendInfo = null;
        Timestamp createDate = null;
        Timestamp startUseTime = null;
        Timestamp endUseTime = null;
        String remark = null;
        int overdueType = OverdueTypeConstant.DELAY;

        if (userFromUserQuery instanceof User) {
            User u = (User) userFromUserQuery;
            nickname = u.getNickname();
            extendInfo = u.getExtendInfo();
            createDate = u.getCreateDate();
            startUseTime = u.getStartUseTime();
            endUseTime = u.getEndUseTime();
            remark = u.getRemark();
            overdueType = u.getOverdueType();
        }

        User user = new User(returnUsername, userFromUserQuery.getPassword(), userFromUserQuery.isEnabled(), true, true, true,
            combinedAuthorities, id, nickname);

        if (extendInfo != null) {
            user.setExtendInfo(extendInfo);
        }

        if (createDate != null) {
            user.setCreateDate(createDate);
        }

        user.setStartUseTime(startUseTime);
        user.setEndUseTime(endUseTime);
        user.setRemark(remark);
        user.setOverdueType(overdueType);

        return user;
    }
    /**
     * Loads authorities by executing the SQL from <tt>authoritiesByUsernameQuery</tt>.
     *
     * @return a list of GrantedAuthority objects for the user
     */
    protected List<GrantedAuthority> loadUserAuthorities(String username) {
        //authoritiesByUsernameQuery
        return getJdbcTemplate().query(DEF_MY_AUTHORITIES_BY_USERNAME_QUERY , new String[] {username}, new RowMapper<GrantedAuthority>() {
            public GrantedAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
                String roleName = getRolePrefix() + rs.getString(2);
                return new SimpleGrantedAuthority(roleName);
            }
        });
    }

    /**
     * Loads authorities by executing the SQL from <tt>groupAuthoritiesByUsernameQuery</tt>.
     *
     * @return a list of GrantedAuthority objects for the user
     */
    protected List<GrantedAuthority> loadGroupAuthorities(String username) {
        //groupAuthoritiesByUsernameQuery
        return getJdbcTemplate().query( DEF_MY_GROUP_AUTHORITIES_BY_USERNAME_QUERY , new String[] {username}, new RowMapper<GrantedAuthority>() {
            public GrantedAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
                 String roleName = getRolePrefix() + rs.getString(3);
                return new SimpleGrantedAuthority(roleName);
            }
        });
    }
    
    protected void addCustomAuthorities(String username, List<GrantedAuthority> authorities) {
        if (authorities.isEmpty()) {
            authorities.add(new SimpleGrantedAuthority("ROLE_DEFLAUT_LOGIN"));
        }
    }
    
    //~ UserDetailsManager implementation ==============================================================================

    public void createUser(final UserDetails user) {
        validateUserDetails(user);
        getJdbcTemplate().update(createUserSql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
            	if( user instanceof User ){
            		User u  = (User)user ;
	                ps.setString(1, u.getUsername());
	                ps.setString(2, u.getPassword());
	                ps.setBoolean(3, u.isEnabled());
	                ps.setString(4, u.getNickname() );
            	}
            }
        });
        if (getEnableAuthorities()) {
            insertUserAuthorities(user);
        }
    }

    public void updateUser(final UserDetails user) {
        validateUserDetails(user);
        getJdbcTemplate().update(updateUserSql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
            	if( user instanceof User ){
            		User u  = (User)user ;
	                ps.setString(1, u.getPassword());
	                ps.setBoolean(2, u.isEnabled());
	                ps.setString(3, u.getNickname());
	                ps.setString(4, u.getUsername());
            	}
            }
        });

        if (getEnableAuthorities()) {
            deleteUserAuthorities(user.getUsername());
            insertUserAuthorities(user);
        }

        userCache.removeUserFromCache(user.getUsername());
    }

    private void insertUserAuthorities(UserDetails user) {
        for (GrantedAuthority auth : user.getAuthorities()) {
            getJdbcTemplate().update(createAuthoritySql, user.getUsername(), auth.getAuthority());
        }
    }

    public void deleteUser(String username) {
        if (getEnableAuthorities()) {
            deleteUserAuthorities(username);
        }
        getJdbcTemplate().update(deleteUserSql, username);
        userCache.removeUserFromCache(username);
    }

    private void deleteUserAuthorities(String username) {
        getJdbcTemplate().update(deleteUserAuthoritiesSql, username);
    }

    public void changePassword(String oldPassword, String newPassword) throws AuthenticationException {
        Authentication currentUser = SecurityContextHolder.getContext().getAuthentication();

        if (currentUser == null) {
            // This would indicate bad coding somewhere
            throw new AccessDeniedException("Can't change password as no Authentication object found in context " +
                    "for current user.");
        }

        String username = currentUser.getName();

        // If an authentication manager has been set, re-authenticate the user with the supplied password.
        if (authenticationManager != null) {
            logger.debug("Reauthenticating user '"+ username + "' for password change request.");

            authenticationManager.authenticate(new UsernamePasswordAuthenticationToken(username, oldPassword));
        } else {
            logger.debug("No authentication manager set. Password won't be re-checked.");
        }

        logger.debug("Changing password for user '"+ username + "'");

        getJdbcTemplate().update(changePasswordSql, newPassword, username);

        SecurityContextHolder.getContext().setAuthentication(createNewAuthentication(currentUser, newPassword));

        userCache.removeUserFromCache(username);
    }

    protected Authentication createNewAuthentication(Authentication currentAuth, String newPassword) {
        UserDetails user = loadUserByUsername(currentAuth.getName());

        UsernamePasswordAuthenticationToken newAuthentication =
                new UsernamePasswordAuthenticationToken(user, user.getPassword(), user.getAuthorities());
        newAuthentication.setDetails(currentAuth.getDetails());

        return newAuthentication;
    }

    public boolean userExists(String username) {
        List<String> users = getJdbcTemplate().queryForList(userExistsSql, new String[] {username}, String.class);

        if (users.size() > 1) {
            throw new IncorrectResultSizeDataAccessException("More than one user found with name '" + username + "'", 1);
        }

        return users.size() == 1;
    }
    
  //~ UserDetailsDao  implementation ==============================================================================
    
    private final RowMapper<User> userMapper = new RowMapper<User>() {
        @Override
        public User mapRow(ResultSet rs, int arg1) throws SQLException {
            
            String username = rs.getString("username");
            String password = rs.getString("password");
            String nickname = rs.getString("nick_name");
            boolean enable= rs.getBoolean( "enabled" );
            User mo = new User( username, password, nickname,  enable );
//            mo.set
            return mo;
        }
    };

    @Override
    public User findUserByUsername(String username) {
        String sql = "select * from users where username = ? ";
        List<User> list = getJdbcTemplate().query(sql, userMapper, username );
        if(list.size()>0){
            return list.get(0);
        }
        return null;
    }

    @Override
    public Map<String, Object> findUserMapByUsername(String username ) {
        String sql = "select * from users where username = ? ";
        List<Map<String,Object>> list = getJdbcTemplate().queryForList(sql,  username );
        if(list.size()>0){
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<User> findUsersByCond(User mo, int start, int pageSize) {
        StringBuffer sql = new StringBuffer("select * from users ");
        Object[] args = gernateUserWhereCond( sql, mo, start, pageSize, false );
        return  getJdbcTemplate().query(sql.toString(), userMapper, args );
    }
    
    private Object[] gernateUserWhereCond(StringBuffer b, User mo, int start, int pageSize, boolean isCount )  {
        List<Object> l = new ArrayList<Object>();
        b.append(" WHERE 1=1 ");
        if (mo != null) {
            if ( StringUtils.isNotBlank(mo.getKeyword()) ) {
                b.append(" AND ").append(" username like ? ");
                l.add( "%"+mo.getKeyword()+"%");
            }
        }
        if(!isCount){	//不是计算count,才可能需要排序和条数限制
            b.append(" ORDER BY ").append(" add_time DESC ");
            if (start < 0 || pageSize < 0) {

            }
            else {
                b.append(" limit ?,?");
                l.add(start);
                l.add(pageSize);
            }
        }
        return l.toArray(new Object[l.size()]);
    }

    @Override
    public List<Map<String, Object>> findUserMapsByCond(User mo, int start, int pageSize) {
        StringBuffer sql = new StringBuffer("select * from users ");
        Object[] args = gernateUserWhereCond( sql, mo, start, pageSize, false );
        return  getJdbcTemplate().queryForList(sql.toString(),  args );
    }

    @Override
    public int findUserCountByCond(User mo) {
        StringBuffer sql = new StringBuffer("select count(1) from users ");
        Object[] args = gernateUserWhereCond( sql, mo, -1, -1, true );
        return  getJdbcTemplate().queryForInt(sql.toString(),  args );
    }


    //~ GroupManager implementation ====================================================================================
    @Override
    public List<String> findAllGroups() {
        return getJdbcTemplate().queryForList(findAllGroupsSql, String.class);
    }

    public List<String> findUsersInGroup(String groupName) {
        Assert.hasText(groupName);
        return getJdbcTemplate().queryForList(findUsersInGroupSql, new String[] {groupName}, String.class);
    }

    public void createGroup(final String groupName, final List<GrantedAuthority> authorities) {
        Assert.hasText(groupName);
        Assert.notNull(authorities);

        logger.debug("Creating new group '" + groupName + "' with authorities " +
                AuthorityUtils.authorityListToSet(authorities));

        getJdbcTemplate().update(insertGroupSql, groupName);

        final int groupId = findGroupId(groupName);

        for (GrantedAuthority a : authorities) {
            final String authority = a.getAuthority();
            getJdbcTemplate().update(insertGroupAuthoritySql, new PreparedStatementSetter() {
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setInt(1, groupId);
                    ps.setString(2, authority);
                }
            });
        }
    }

    public void deleteGroup(String groupName) {
        logger.debug("Deleting group '" + groupName + "'");
        Assert.hasText(groupName);

        final int id = findGroupId(groupName);
        PreparedStatementSetter groupIdPSS = new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, id);
            }
        };
        getJdbcTemplate().update(deleteGroupMembersSql, groupIdPSS);
        getJdbcTemplate().update(deleteGroupAuthoritiesSql, groupIdPSS);
        getJdbcTemplate().update(deleteGroupSql, groupIdPSS);
    }

    public void renameGroup(String oldName, String newName) {
        logger.debug("Changing group name from '" + oldName + "' to '" + newName + "'");
        Assert.hasText(oldName);
        Assert.hasText(newName);

        getJdbcTemplate().update(renameGroupSql, newName, oldName);
    }

    public void addUserToGroup(final String username, final String groupName) {
        logger.debug("Adding user '" + username + "' to group '" + groupName + "'");
        Assert.hasText(username);
        Assert.hasText(groupName);

        final int id = findGroupId(groupName);
        getJdbcTemplate().update(insertGroupMemberSql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, id);
                ps.setString(2, username);
            }
        });

        userCache.removeUserFromCache(username);
    }

    public void removeUserFromGroup(final String username, final String groupName) {
        logger.debug("Removing user '" + username + "' to group '" + groupName + "'");
        Assert.hasText(username);
        Assert.hasText(groupName);

        final int id = findGroupId(groupName);

        getJdbcTemplate().update(deleteGroupMemberSql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, id);
                ps.setString(2, username);
            }
        });

        userCache.removeUserFromCache(username);
    }

    public List<GrantedAuthority> findGroupAuthorities(String groupName) {
        logger.debug("Loading authorities for group '" + groupName + "'");
        Assert.hasText(groupName);

        return getJdbcTemplate().query(groupAuthoritiesSql, new String[] {groupName}, new RowMapper<GrantedAuthority>() {
            public GrantedAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
                 String roleName = getRolePrefix() + rs.getString(3);

                return new SimpleGrantedAuthority(roleName);
            }
        });
    }

    public void removeGroupAuthority(String groupName, final GrantedAuthority authority) {
        logger.debug("Removing authority '" + authority + "' from group '" + groupName + "'");
        Assert.hasText(groupName);
        Assert.notNull(authority);

        final int id = findGroupId(groupName);

        getJdbcTemplate().update(deleteGroupAuthoritySql, new PreparedStatementSetter() {

            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, id);
                ps.setString(2, authority.getAuthority());
            }
        });
    }

    public void addGroupAuthority(final String groupName, final GrantedAuthority authority) {
        logger.debug("Adding authority '" + authority + "' to group '" + groupName + "'");
        Assert.hasText(groupName);
        Assert.notNull(authority);

        final int id = findGroupId(groupName);
        getJdbcTemplate().update(insertGroupAuthoritySql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, id);
                ps.setString(2, authority.getAuthority());
            }
        });
    }

    private int findGroupId(String group) {
        return getJdbcTemplate().queryForInt(findGroupIdSql, group);
    }

    //~ GroupDao implementation ====================================================================================
    
    @Override
    public int removeGroupAuthoritysById( String id ){
//    	int id = findGroupId(groupName);
    	String sql = " delete from group_authorities where group_id = ? ";
    	return getJdbcTemplate().update(sql, id );
    }
    
    @Override
    public int addGroupAuthoritys( final String id, final List<GrantedAuthority> authoritys ){
//    	final int id = findGroupId(groupName);
    	if ( getJdbcTemplate().queryForInt("select count(1) from groups where id = ?", id) == 0 ){
    		return 0 ;
    	};
    	getJdbcTemplate().batchUpdate(insertGroupAuthoritySql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setLong(1,  Long.parseLong(id) );
				ps.setString(2, authoritys.get(i).getAuthority() );
			}
			@Override
			public int getBatchSize() {
				return authoritys.size() ;
			}
    	});
    	return 1;
    }
    

    @Override
    public List<GrantedAuthority> findGroupAuthoritiesById(String groupId){
        Assert.hasText(groupId);
        final String sql = "select g.id, g.group_name, ga.authority " +
                "from groups g, group_authorities ga " +
                "where g.id = ? " +
                "and g.id = ga.group_id ";
        
        return getJdbcTemplate().query(sql, new String[] {groupId}, new RowMapper<GrantedAuthority>() {
            public GrantedAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
                 String roleName = getRolePrefix() + rs.getString(3);
                return new SimpleGrantedAuthority(roleName);
            }
        });
    }
    
    @Override
    public void addUserToGroupById(String username, String groupId ){
        logger.debug("Adding username '" + username + "' to groupId '" + groupId + "'");
        if( StringUtils.isBlank(username) || StringUtils.isBlank(groupId) ){
        	return ;
        }
    	String sql = "insert into group_members (group_id, username) values (?,?)";
    	getJdbcTemplate().update(sql, groupId, username );
    }
    
    @Override
    public void removeUserFromGroupById(final String username, final String groupId) {
        logger.debug("Adding username '" + username + "' to groupId '" + groupId + "'");
        if( StringUtils.isBlank(username) && StringUtils.isBlank(groupId) ){
        	return ;
        }
//        final int id = findGroupId(groupName);
        getJdbcTemplate().update(deleteGroupMemberSql, groupId, username );
        userCache.removeUserFromCache(username);
    }
    
    @Override
    public void removeUserFromAllGroup( String username  ){
    	getJdbcTemplate().update("delete from group_members where username = ? ",  username );
    }
    
    @Override
    public RoleGroup findRoleGroupByName(String groupname ){
        String sql = "";
        return null;
    }

    @Override
    public Map<String, Object> findRoleGroupMapByName(String groupname ){
        String sql = "select * from groups where group_name = ? ";
        List<Map<String,Object>> list = getJdbcTemplate().queryForList(sql, groupname );
        if( list.size() > 0){
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<RoleGroup> findRoleGroupsByCond(RoleGroup mo, int start, int pageSize){
        return null ;
    }
    
    private Object[] gernateRoleGroupWhereCond(StringBuffer b, RoleGroup mo, int start, int pageSize, boolean isCount ) {
    	//多对多
    	if (mo != null) {
        	if(StringUtils.isNotBlank( mo.getS_username()) ){
        		int from_flag = b.indexOf(" from ");
        		if(!isCount){
    				b.insert(from_flag, ", gm_rel.* ");
        		}
        		b.append(" LEFT JOIN group_members gm_rel ON gm_rel.group_id = g.id " );
        	}
        }
    	
        List<Object> l = new ArrayList<Object>();
        
        b.append(" WHERE 1=1 ");
        if (mo != null) {
        	if(StringUtils.isNotBlank( mo.getS_username()) ){
        		b.append(" and gm_rel.username = ? ");
        		l.add( mo.getS_username() );
        	}
        }
        
        if(!isCount){
	        b.append(" ORDER BY ").append(" g.id asc ");
	        if (start < 0 || pageSize < 0) {
	
	        }
	        else {
	            b.append(" limit ?,?");
	            l.add(start);
	            l.add(pageSize);
	        }
        }
        return l.toArray(new Object[l.size()]);
    }
    
    @Override
    public List<Map<String, Object>> findRoleGroupMapsByCond(RoleGroup mo, int start, int pageSize){
        StringBuffer b = new StringBuffer("select g.*, g.id gid from groups g ");
        Object[] args = gernateRoleGroupWhereCond(b , mo, start, pageSize, false );
        return getJdbcTemplate().queryForList( b.toString() , args );
    }

    @Override
    public int findRoleGroupCountByCond(RoleGroup mo){
        StringBuffer b = new StringBuffer("select count(1) from groups g ");
        Object[] args = gernateRoleGroupWhereCond(b , mo, -1, -1, true );
        return getJdbcTemplate().queryForInt( b.toString() , args );
    }

    @Override
    public int modifyRoleGroup( RoleGroup mo ){
    	String sql = "update groups set group_name=? where id= ?";
    	return getJdbcTemplate().update(sql, mo.getGroupname(), mo.getId());
    }
    
  //~ getter and setter  ====================================================================================
    
    public void setAuthenticationManager(AuthenticationManager authenticationManager) {
        this.authenticationManager = authenticationManager;
    }

    public void setCreateUserSql(String createUserSql) {
        Assert.hasText(createUserSql);
        this.createUserSql = createUserSql;
    }

    public void setDeleteUserSql(String deleteUserSql) {
        Assert.hasText(deleteUserSql);
        this.deleteUserSql = deleteUserSql;
    }

    public void setUpdateUserSql(String updateUserSql) {
        Assert.hasText(updateUserSql);
        this.updateUserSql = updateUserSql;
    }

    public void setCreateAuthoritySql(String createAuthoritySql) {
        Assert.hasText(createAuthoritySql);
        this.createAuthoritySql = createAuthoritySql;
    }

    public void setDeleteUserAuthoritiesSql(String deleteUserAuthoritiesSql) {
        Assert.hasText(deleteUserAuthoritiesSql);
        this.deleteUserAuthoritiesSql = deleteUserAuthoritiesSql;
    }

    public void setUserExistsSql(String userExistsSql) {
        Assert.hasText(userExistsSql);
        this.userExistsSql = userExistsSql;
    }

    public void setChangePasswordSql(String changePasswordSql) {
        Assert.hasText(changePasswordSql);
        this.changePasswordSql = changePasswordSql;
    }

    public void setFindAllGroupsSql(String findAllGroupsSql) {
        Assert.hasText(findAllGroupsSql);
        this.findAllGroupsSql = findAllGroupsSql;
    }

    public void setFindUsersInGroupSql(String findUsersInGroupSql) {
        Assert.hasText(findUsersInGroupSql);
        this.findUsersInGroupSql = findUsersInGroupSql;
    }

    public void setInsertGroupSql(String insertGroupSql) {
        Assert.hasText(insertGroupSql);
        this.insertGroupSql = insertGroupSql;
    }

    public void setFindGroupIdSql(String findGroupIdSql) {
        Assert.hasText(findGroupIdSql);
        this.findGroupIdSql = findGroupIdSql;
    }

    public void setInsertGroupAuthoritySql(String insertGroupAuthoritySql) {
        Assert.hasText(insertGroupAuthoritySql);
        this.insertGroupAuthoritySql = insertGroupAuthoritySql;
    }

    public void setDeleteGroupSql(String deleteGroupSql) {
        Assert.hasText(deleteGroupSql);
        this.deleteGroupSql = deleteGroupSql;
    }

    public void setDeleteGroupAuthoritiesSql(String deleteGroupAuthoritiesSql) {
        Assert.hasText(deleteGroupAuthoritiesSql);
        this.deleteGroupAuthoritiesSql = deleteGroupAuthoritiesSql;
    }

    public void setDeleteGroupMembersSql(String deleteGroupMembersSql) {
        Assert.hasText(deleteGroupMembersSql);
        this.deleteGroupMembersSql = deleteGroupMembersSql;
    }

    public void setRenameGroupSql(String renameGroupSql) {
        Assert.hasText(renameGroupSql);
        this.renameGroupSql = renameGroupSql;
    }

    public void setInsertGroupMemberSql(String insertGroupMemberSql) {
        Assert.hasText(insertGroupMemberSql);
        this.insertGroupMemberSql = insertGroupMemberSql;
    }

    public void setDeleteGroupMemberSql(String deleteGroupMemberSql) {
        Assert.hasText(deleteGroupMemberSql);
        this.deleteGroupMemberSql = deleteGroupMemberSql;
    }

    public void setGroupAuthoritiesSql(String groupAuthoritiesSql) {
        Assert.hasText(groupAuthoritiesSql);
        this.groupAuthoritiesSql = groupAuthoritiesSql;
    }

    public void setDeleteGroupAuthoritySql(String deleteGroupAuthoritySql) {
        Assert.hasText(deleteGroupAuthoritySql);
        this.deleteGroupAuthoritySql = deleteGroupAuthoritySql;
    }

    /**
     * Optionally sets the UserCache if one is in use in the application.
     * This allows the user to be removed from the cache after updates have taken place to avoid stale data.
     *
     * @param userCache the cache used by the AuthenticationManager.
     */
    public void setUserCache(UserCache userCache) {
        Assert.notNull(userCache, "userCache cannot be null");
        this.userCache = userCache;
    }

    
    //------------------- some private function
    private void validateUserDetails(UserDetails user) {
        Assert.hasText(user.getUsername(), "Username may not be empty or null");
        validateAuthorities(user.getAuthorities());
    }

    private void validateAuthorities(Collection<? extends GrantedAuthority> authorities) {
        Assert.notNull(authorities, "Authorities list must not be null");

        for (GrantedAuthority authority : authorities) {
            Assert.notNull(authority, "Authorities list contains a null entry");
            Assert.hasText(authority.getAuthority(), "getAuthority() method must return a non-empty string");
        }
    }

  

}
